import time
import numpy as np
import pymysql
import pandas as pd
from datetime import datetime
import pymysql.cursors

class MysqlUtils(object):
    def __init__(self, *args):
        self.conn = pymysql.connect(
            host="127.0.0.1",
            user="root",
            passwd="root",
            db="scenic",
            port=3306,
            charset="utf8"
        )

    def get_scenic_data(self):
        """
        获取数据
        """
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT
            order_id,
            age,
            user_count,
            id_no,
            user_name,
            phone,
            CASE
                WHEN LENGTH(u.id_no) - 18 THEN YEAR(NOW()) - CAST(SUBSTR(u.id_no, 7, 4) as SIGNED)
                ELSE NULL
            END AS age,
            (SELECT count(*) FROM ticket_order_user_rel WHERE order_id = o.id) as user_count
        FROM ticket_order o join ticket_order_user_rel u on u.order_id = o.id WHERE u.id_no is not null and u.id_no != ''
        WHERE user_count = 1 and (age < 18 or age >= 60)
        """
        cursor.execute(sql)
        result = cursor.fetchall()
        return result

    def calculate_baseline(df, current_month, window_size=6):
        # 提取当月数据
        df_current = df[df['month'] == current_month]

        # 提取历史数据
        history_start = current_month - pd.DateOffset(month=window_size)
        df_history = df[(df['month'] >= history_start) & (df['month'] < current_month)]

        # 按城市计算均值和标准差
        df_baseline = df_history.groupby('city_name')['visitor_count'].agg(['mean', 'std']).reset_index()
        df_baseline.rename(columns={'mean': 'hist_mean', 'std': 'hist_std'}, inplace=True)

        # 合并当月数据
        df_merged = df_current.merge(df_baseline, on='city_name', how='left')
        return df_merged

# 假设当前月是2024-12
current_month = pd.to_datetime('2024-12-01')
df_city_monthly = pd.DataFrame({
    'city_name': ['city1', 'city2', 'city3'],
    'month': [pd.to_datetime('2024-06'), pd.to_datetime('2024-07'), pd.to_datetime('2024-08')],
    'visitor_count': [100, 150, 200]
})

df_merged = calculate_baseline(df_city_monthly, current_month)

# 标记爆发增长的城市（z_score > 3 or z_score < -3）
df_merged['z_score'] = (df_merged['visitor_count'] - df_merged['hist_mean']) / df_merged['hist_std']
df_increased = df_merged[df_merged['z_score'] > 3]
df_reduced = df_merged[df_merged['z_score'] < -3]

print(df_increased)
print('-------------------------')
print(df_reduced)